package com.atguigu.jdbc;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 798133716@qq.com
 * @create 2021-02-26 19:18
 */
public class CRUDDemo {

    //        向表中添加数据
    @Test
    public void test() throws SQLException {
    //1获取连接对象
        Connection connection = JDBCUtils.getConnection();
        //2预编译
        String sql ="insert into demo1(id,username) values(?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        //3占位符赋值
        ps.setInt(1,2);
        ps.setString(2,"zy");
        //4执行SQL语句
        int i = ps.executeUpdate();
        System.out.println("有" + i + "条数据受到影响");
//        5关闭资源
        JDBCUtils.close(ps,connection);
    }
    /*
    修改数据
     */
    @Test
    public void test1() throws SQLException {
//        1获取连接
        Connection connection = JDBCUtils.getConnection();
//        2预编译
        String sql = "update demo1 set username=? where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
//        3赋值
        ps.setString(1,"zywan");
        ps.setInt(2,2);
        //4执行
        int i = ps.executeUpdate();
        System.out.println("有" + i + "条数据受到影响");
        JDBCUtils.close(ps,connection);

    }
    /*
    删
     */
    @Test
    public void test2() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "delete from demo1";
        PreparedStatement ps = connection.prepareStatement(sql);
        int i = ps.executeUpdate();
        System.out.println(i);
        JDBCUtils.close(ps,connection);
    }
    /*
    查
     */
    @Test
    public void test3() throws SQLException {
        List<Employee> resultSet = getResultSet();
        resultSet.forEach(System.out::println);
    }
    public  List<Employee> getResultSet() throws SQLException {
        List<Employee> list = new ArrayList<>();
        Connection connection = JDBCUtils.getConnection();
        //预编译
        String sql = "select id,username from demo1";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            Employee employee = new Employee(id, username);
            list.add(employee);
        }
        JDBCUtils.close(ps,connection,rs);
        return list;
    }
}
